/* This file prepares CRSP return data */
/* 
Input files: CRSP_07_18 CRSP_18_19 CRSP2020jan_jun 
Output files: mktdate_0719 permno_permco10_19 permno_ncusip_match crsp_partial crsp_calcs_temp crsp1019_ret crsp1019_ret_asvi crsp_returns
*/



**********************************************
clear all
macro drop _all
scalar drive		= "E:"
scalar maindir		= "`=drive'\Replicate"
scalar tabledir		= "`=drive'\Replicate\TablesRep"
scalar crspdir		= "`=drive'\Replicate"
cd "`=crspdir'"
**********************************************

use CRSP_18_19, clear // already sorted by permno date
keep if date>td(31dec2018)
append using CRSP2020jan_jun 
append using CRSP_07_18 
keepvar permno permco date ticker tsymbol comnam prc vol ret vwretd naics siccd hsiccd cusip ncusip shrout shrcd , asis 
label var ticker "CRSP ticker"
gduplicates drop
drop if permco==56193 | permco==50852  // the only case where same permno has 2 permcos and this occurs many times: tickers are different but comnam is WISDOMTREE TRUST (an ETF sponsor)

	preserve
	keep date
	gduplicates drop
	sort date
	gen int mktdate=_n
	label var mktdate "excludes weekends, = xth trading day since 1st day of 2007->end of 2019"
	cd "`=maindir'"
	save mktdate_0719, replace
	cd "`=crspdir'"
	save mktdate_0719, replace
	restore

merge m:1 date using mktdate_0719, nogen
gduplicates report date permno // none
save temp_crsp_append, replace


use temp_crsp_append, clear

			preserve
			drop prc vol ret 
			sort permno date
			g year=year(date)
			g month=month(date)
			g ym=ym(year,month)
			format ym %tm
			drop if ym<tm(2010m1)
			drop year month ym
			* gduplicates report date permno // none
				cd "`=maindir'"
				save permno_permco10_19, replace // used in 0_permno_symbol_match.do
				cd "`=crspdir'"
			drop if mi(ncusip) 
			gen ncusip6=substr(ncusip,1,6)
			gduplicates drop date ncusip6, force
			sort date ncusip6
			cd "`=maindir'"
			save permno_ncusip_match, replace // used in 0_prep_ravenpack.do 
			cd "`=crspdir'"
			restore
drop cusip ncusip


tsset permno mktdate 
gen xret=ret-vwretd

forvalues i=1/2 {
gen xret_f`i'=f`i'.xret
	label var xret_f`i' "Excess Return forward `i' days"
}
gen xret_3day= (1+xret)*(1+xret_f1)*(1+xret_f2)-1
	label var xret_3day "Cum. 3 day ret-vwMkt return from t to t+2 inclusive"
drop xret_f*


replace prc=prc*-1 if prc<0  
gen double mktcap=prc*shrout
	label var mktcap "prc x shrout"

keep if date>=td(01jan2009)
gsort date permno
save crsp_partial, replace




cd "`=crspdir'"
use date mktdate permno ret vwretd vol shrout using crsp_partial, clear 

tsset permno mktdate 

gen double logturn=ln(vol/(shrout*1000))
	label var logturn "ln(volume)"
gen double logturn_l1=l1.logturn
drop vol shrout	

tsegen double meanlogturn_140_20 = rowmean(L(20/140).logturn, 60)  // require 60 days, o/w missing

gen double ablogvol = logturn - meanlogturn_140_20
	label var ablogvol "lnTurn_t - lnTurn_t-140 to t-20 i.e. 6m skipping 1m"
gen double ablogvol_l1 = l1.ablogvol

forvalues k=1/30 { 
gen double logturn_f`k' = f`k'.logturn
gen double ablogvol_f`k' = logturn_f`k' - meanlogturn_140_20
drop logturn_f`k' 
}


gen double abret=ret - vwretd
	label var abret "Abnormal returns Cookson&Niessner JF definition"
tsegen double sdabret_l1to5 = rowsd(L(1/5).abret)
	label var sdabret_l1to5 "Stock's sd of returns last 5 trading days"

	
gen double ln_abret = ln(abret+1)
bys permno (mktdate): gen double cum_sum = sum(ln_abret)  
by  permno: gen double cabret_l1to5 = exp(cum_sum[_n-1] - cum_sum[_n-6])-1 
	label var cabret_l1to5 "Cumulative abnormal return t-1 to t-5"
by  permno: gen double cabret_l6to30 = exp(cum_sum[_n-6] - cum_sum[_n-31])-1 
	label var cabret_l6to30 "Cumulative abnormal return t-6 to t-30"
drop ln_abret cum_sum	
gsort date permno
compress
save crsp_calcs_temp, replace




use crsp_calcs_temp, clear

merge 1:1 date permno using crsp_partial, nogen



ren ticker ticker_crsp
ren comnam name_crsp

keep if date>=td(01jan2010)
drop naics siccd hsiccd vol abret vwretd meanlogturn_140_20 ret  
order permco permno date mktdate ticker_crsp tsymbol name_crsp shrcd mktcap
sort date ticker
cd "`=maindir'"
save crsp1019_ret, replace



cd "`=maindir'"
use crsp1019_ret, clear

merge m:1 date ticker_crsp using gASVI_data, update

g year=year(date)
g month=month(date)
g ym=ym(year,month)
format ym %tm

tab ym _merge  
drop if _merge==2
drop _merge year month shrout
sort date permno
compress
save crsp1019_ret_asvi, replace

***********************************************************************************************************************************************************************************************
***********************************************************************************************************************************************************************************************





/* Prep future returns data for 3b_returns (Table11) and 3c_ret_stockday (Table13b) */

cd "`=crspdir'"
use CRSP_18_19, clear 
keep if date>td(31dec2018)
append using CRSP2020jan_jun 
append using CRSP_07_18 
cd "`=maindir'"

keepvar permno date ret vwretd shrcd , asis 
keep if date>td(01jan2013)
keep if inlist(shrcd,10,11,12) 
drop if inlist(.,ret) | inlist(.c,ret)
gduplicates drop
drop shrcd 


merge m:1 date using mktdate_0719, nogen  // made above (line 27)
gunique date permno

compress
save temp_crsp_append1, replace


use permno date asvi using crsp1019_ret_asvi, clear
replace asvi=ln(1+asvi) 

merge 1:1 permno date using temp_crsp_append1, keep(using match) nogen



tsset permno mktdate  
gen xret=ret-vwretd
gen xret_l1=l1.xret

forvalues i=2/31 {
gen xret_f`i'=f`i'.xret 
	label var xret_f`i' "Excess Return forward `i' days"
}
drop ret mktdate vwretd

gen double xret_2day_f2= ((1+xret_f3)*(1+xret_f2)-1)
gen double xret_3day_f2= ((1+xret_f4)*(1+xret_f3)*(1+xret_f2)-1)
gen double xret_5day_f2= ((1+xret_f6)*(1+xret_f5)*(1+xret_f4)*(1+xret_f3)*(1+xret_f2)-1)
gen double xret_10day_f2= (1+xret_f11)*(1+xret_f10)*(1+xret_f9)*(1+xret_f8)*(1+xret_f7)*(1+xret_f6)*(1+xret_f5)*(1+xret_f4)*(1+xret_f3)*(1+xret_f2)-1
gen double xret_15day_f2= (1+xret_f16)*(1+xret_f15)*(1+xret_f14)*(1+xret_f13)*(1+xret_f12)*(1+xret_f11)*(1+xret_f10)*(1+xret_f9)*(1+xret_f8)*(1+xret_f7)*(1+xret_f6)*(1+xret_f5)*(1+xret_f4)*(1+xret_f3)*(1+xret_f2)-1
gen double xret_30day_f2= (1+xret_f31)*(1+xret_f30)*(1+xret_f29)*(1+xret_f28)*(1+xret_f27)*(1+xret_f26)*(1+xret_f25)*(1+xret_f24)*(1+xret_f23)*(1+xret_f22)*(1+xret_f21)*(1+xret_f20)*(1+xret_f19)*(1+xret_f18)*(1+xret_f17)*(1+xret_f16)*(1+xret_f15)*(1+xret_f14)*(1+xret_f13)*(1+xret_f12)*(1+xret_f11)*(1+xret_f10)*(1+xret_f9)*(1+xret_f8)*(1+xret_f7)*(1+xret_f6)*(1+xret_f5)*(1+xret_f4)*(1+xret_f3)*(1+xret_f2)-1
gen double xret_10d_f2= (1+xret_f10)*(1+xret_f9)*(1+xret_f8)*(1+xret_f7)*(1+xret_f6)*(1+xret_f5)*(1+xret_f4)*(1+xret_f3)*(1+xret_f2)-1
	foreach k in xret_2day_f2 xret_3day_f2 xret_5day_f2 xret_10day_f2 xret_15day_f2 xret_30day_f2 xret_10d_f2 {  // *100 for interpretation (all returns variables)
	replace `k' = `k'*100
	} 

sort permno date
order permno date xret*
save crsp_returns, replace  // used in 3b_returns.do & in 3c_ret_stockday.do




foreach file_to_delete in temp_crsp_append temp_crsp_append1  {
cap n erase `file_to_delete'.dta
}



